SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[_DrugDispo_ReplaceCode]
    @NewDrugId NVARCHAR(20) ,
    @OldDrugId NVARCHAR(20)
AS 
    BEGIN
        INSERT  INTO dbo.DrugDispo
                ( DrugId ,
                  DrugName ,
                  GenericName ,
                  Specification ,
                  Unit ,
                  MarkUpType ,
                  CostPrice ,
                  SellPrice ,
                  ValidPeriod ,
                  MinStock ,
                  MaxStock ,
                  Shelf ,
                  GroupId ,
                  ClassificationId ,
                  UseGuide ,
                  Precaution ,
                  Package ,
                  Remark
		        
                )
                SELECT  @NewDrugId ,
                        DrugName ,
                        GenericName ,
                        Specification ,
                        Unit ,
                        MarkUpType ,
                        CostPrice ,
                        SellPrice ,
                        ValidPeriod ,
                        MinStock ,
                        MaxStock ,
                        Shelf ,
                        GroupId ,
                        ClassificationId ,
                        UseGuide ,
                        Precaution ,
                        Package ,
                        Remark
                FROM    dbo.DrugDispo
                WHERE   DrugId = @OldDrugId
        

        UPDATE  dbo.GRNDetails
        SET     DrugId = @NewDrugId
        WHERE   DrugId = @OldDrugId
        
        UPDATE  dbo.PODetails
        SET     DrugId = @NewDrugId
        WHERE   DrugId = @OldDrugId
        
        UPDATE  dbo.PrescriptionDetail
        SET     DrugId = @NewDrugId
        WHERE   DrugId = @OldDrugId
        
        UPDATE  dbo.Stock
        SET     DrugId = @NewDrugId
        WHERE   DrugId = @OldDrugId
        
        UPDATE  dbo.StockOutDetails
        SET     DrugId = @NewDrugId
        WHERE   DrugId = @OldDrugId
        
        UPDATE dbo.DrugDiscount 
        SET     DrugId = @NewDrugId
        WHERE   DrugId = @OldDrugId
        
        DELETE  FROM dbo.DrugDispo
        WHERE   DrugId = @OldDrugId
    END
GO
